#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Datetime: 2020/7/28 14:46
# @Author  : WEN Jing
# @Site    :
# @File    : VaR_analysis.py
# @Software: PyCharm

"""
脚本说明: 投资组合VaR分析，包括整体的VaR分析，和成分的VaR分析
"""

import pandas as pd
import numpy as np
from scipy.stats import norm
from quant_researcher.quant.project_tool.db_operator import db_conn
from quant_researcher.quant.project_tool.time_tool import date_shifter, format_date_str
from quant_researcher.quant.project_tool.celebrity import get_lst_condition
from quant_researcher.quant.project_tool.df_tool import calendar_reindex
from quant_researcher.quant.datasource_fetch.portfolio_api.portfolio_tool import get_portfolio_fund_weight, \
    get_portfolio_nav_related_info
from quant_researcher.quant.project_tool.exception import FOFServerError, ERR_S_2, FOFUserError, ERR_U_2

FUND_NAV_TABLE = 'mf_di_fndadjnav'
FUND_NAME_TABLE = 'mf_bd_fndinfo'
INDEX_HQ_TABLE = 'mf_di_idxdailyreturn'


def var_analysis(portfolio_id, index_code, calc_date, forecast_period, forecast_freq, confidence_level):
    """

    :param portfolio_id: str，组合id
    :param index_code: str，基准指数代码
    :param calc_date: str，计算日期，格式形如'2020-07-09'
    :param forecast_period: str，预测期限，可选'1'、'2'、'3'、'4'、'5'
    :param forecast_freq: str，预测频率，可选'D'(日度)、'M'(月度)、'Q'(季度)
    :param confidence_level: str，置信度水平，可选'0.99'、'0.95'、'0.90'
    :return:
    """
    # 根据组合代码找到持有的基金
    tmp_df = get_portfolio_fund_weight(portfolio_id, calc_date.replace('-', ''))
    if tmp_df is None:
        raise FOFServerError(ERR_S_2, f"该组合 {portfolio_id} 的id有问题")

    fund_list = tmp_df['fund_code'].tolist()
    # 获取数据：成分基金收益率，成分基金业绩比较基准，基准收益率，成分基金主动收益
    conn_base = db_conn.get_basic_data_conn()
    conn_ty = db_conn.get_derivative_data_conn()

    forecast_period = int(forecast_period)
    if forecast_freq == 'D':
        forecast_multiple = 1
    elif forecast_freq == 'M':
        forecast_multiple = 30
    else:
        forecast_multiple = 90
    forecast_period = forecast_period * forecast_multiple
    alpha = round(1 - float(confidence_level), 2)
    start_date = date_shifter(calc_date, 'years', -3)
    start_date_t = start_date.replace('-', '')
    calc_date_t = calc_date.replace('-', '')

    # 获取组合净值，计算组合收益率
    portfolio_nav = get_portfolio_nav_related_info(portfolio_id, start_date_t, calc_date_t)
    if portfolio_nav is None:
        raise FOFServerError(ERR_S_2, f"未找到该组合 {portfolio_id} 的净值信息")
    portfolio_nav = portfolio_nav[['trade_date', 'account_net_value']]
    portfolio_nav = portfolio_nav.rename(columns={'trade_date': 'tj', 'account_net_value': 'nav'})
    portfolio_nav['tj'] = portfolio_nav['tj'].apply(lambda x: format_date_str(x, '%Y-%m-%d'))
    portfolio_nav = portfolio_nav.set_index('tj')
    portfolio_ret = portfolio_nav.pct_change(forecast_period)
    portfolio_ret = portfolio_ret.dropna().rename(columns={'nav': 'ret'})
    T = len(portfolio_ret.index)
    if T < 2:
        conn_base.close()
        conn_ty.close()
        raise FOFUserError(ERR_U_2, f"该组合{portfolio_id}的收益率序列少于计算的最低要求，无法计算")

    # 获取基金净值，计算基金收益率
    fund_where_condition = get_lst_condition(fund_list, 'fund_code')
    fund_nav = pd.read_sql(f"select fund_code, nav_date as tj, adjusted_nav as nav "
                           f"from {FUND_NAV_TABLE} "
                           f"where {fund_where_condition} "
                           f"and nav_date >=  '{start_date}' "
                           f"and nav_date <= '{calc_date}' ", conn_ty)
    fund_nav['tj'] = fund_nav['tj'].astype(str)
    fund_nav = fund_nav.set_index(['tj', 'fund_code'])['nav'].unstack()
    fund_nav = calendar_reindex(fund_nav)
    fund_ret = fund_nav.ffill().pct_change(forecast_period).where(fund_nav.notna())
    fund_ret = fund_ret.reindex(portfolio_ret.index)
    fund_ret = fund_ret.fillna(0)

    # 获取基金名称
    fund_name = pd.read_sql(f"select fund_code, fund_sname "
                            f"from {FUND_NAME_TABLE} "
                            f"where {fund_where_condition} ", conn_base)

    # 获取指数收盘价，计算指数收益率
    index_hq = pd.read_sql(f"select day as tj, price_to as close "
                           f"from {INDEX_HQ_TABLE} "
                           f"where index_code = '{index_code}' "
                           f"and day >= '{start_date}' "
                           f"and day <= '{calc_date}' ", conn_ty)
    index_hq['tj'] = index_hq['tj'].astype(str)
    index_hq = index_hq.set_index('tj')
    index_ret = index_hq.pct_change(forecast_period)
    index_ret = index_ret.reindex(portfolio_ret.index)
    index_ret = index_ret.fillna(0).rename(columns={'close': 'ret'})

    r_fund = fund_ret
    r_benchmark = pd.DataFrame(index=r_fund.index, columns=fund_list, dtype=float)
    for f in fund_list:
        r_benchmark[f] = index_ret['ret']
    # 成分基金主动收益，组合主动收益
    alphar_fund = r_fund - r_benchmark
    alphar_portfolio = portfolio_ret - index_ret
    # 组合收益率均值方差
    return_p = portfolio_ret['ret'].values
    sigma_p = portfolio_ret['ret'].std()
    return_alpha = (portfolio_ret['ret'] - index_ret['ret']).values
    sigma_alpha = alphar_portfolio['ret'].std()
    data_fund = pd.DataFrame(data={f: [r_fund[f].mean(), r_fund[f].std(ddof=0)] for f in fund_list},
                             index=['mean', 'std'])  # 成分基金收益率的均值和方差
    data_alphafund = pd.DataFrame(data={f: [alphar_fund[f].mean(), alphar_fund[f].std(ddof=0)] for f in fund_list},
                                  index=['mean', 'std'])  # 成分基金主动收益率的均值和方差

    N = norm.ppf(alpha)

    # 计算投资组合的VaR，CVaR，主动VaR,主动CVaR
    VaR_p = -return_p.mean() - sigma_p * N
    CVaR_p = -return_p.mean() + alpha ** -1 * norm.pdf(N) * sigma_p
    alphaVaR_p = -return_alpha.mean() - sigma_alpha * N
    alphaCVaR_p = -return_alpha.mean() + alpha ** -1 * norm.pdf(N) * sigma_alpha

    # 计算成分基金的VaR，CVaR，主动VaR,主动CVaR
    VaR_fund = data_fund.T
    VaR_fund['VaR'] = -VaR_fund['mean'] - VaR_fund['std'] * N
    VaR_fund['CVaR'] = -VaR_fund['mean'] + alpha ** -1 * norm.pdf(N) * VaR_fund['std']

    alphaVaR_fund = data_alphafund.T
    alphaVaR_fund['a_VaR'] = -alphaVaR_fund['mean'] - alphaVaR_fund['std'] * N
    alphaVaR_fund['a_CVaR'] = -alphaVaR_fund['mean'] + alpha ** -1 * norm.pdf(N) * alphaVaR_fund['std']

    # 计算VaR分解指标：边际VaR指标
    cov_calculation = {f: np.cov(return_p, np.array(r_fund[f]))[0][1] for f in fund_list}
    M_VaR_fund = data_fund.T
    M_VaR_fund['cov'] = pd.Series(cov_calculation)
    M_VaR_fund['m_VaR'] = -N / sigma_p * M_VaR_fund['cov'] - return_p.mean()

    # 整理输出信息
    result_portfolio = [{'portfolio_id': portfolio_id, 'VaR': round(VaR_p, 4), 'CVaR': round(CVaR_p, 4),
                         'a_VaR': round(alphaVaR_p, 4), 'a_CVaR': round(alphaCVaR_p, 4)}]
    result_portfolio = {'portfolio': result_portfolio}
    result_fund = pd.concat([VaR_fund[['VaR', 'CVaR']], alphaVaR_fund[['a_VaR', 'a_CVaR']],
                             M_VaR_fund[['m_VaR']]], axis=1, ignore_index=False, sort=False)
    result_fund = result_fund.round(4)
    result_fund = result_fund.reset_index().rename(columns={'index': 'fund_code'})
    result_fund = result_fund.merge(fund_name, how='left', on='fund_code')
    result_fund = result_fund.to_dict(orient='records')
    result_fund = {'fund': result_fund}
    result_all = [result_portfolio, result_fund]

    conn_base.close()
    conn_ty.close()
    return result_all


if __name__ == '__main__':
    var_analysis(portfolio_id='291', index_code='000300', calc_date='2020-07-09',
                 forecast_period='1', forecast_freq='M', confidence_level='0.95')
